# Copyright (C) 2005-2006 Frederic Back <fredericback@gmail.com>
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by the
# Free Software Foundation; either version 2, or (at your option) any later
# version.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTIBILITY
# or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU General Public License
# for more details.

import os
import gtk
import time
import gobject
import threading
import string

try: import psycopg
except: import psycopg2.psycopg1 as psycopg

import GeoTypes

from _dbtools import *
from _options import Options
from _guitools import *
from GeoTypes import Box, Point

#----------------------------------------------------------------------------------------
class TableInformer( gobject.GObject ):
    """ Retrieve informations about tables. """

    __gsignals__ = {
        'minimal-info-gathered': (gobject.SIGNAL_RUN_LAST, gobject.TYPE_NONE, ()),
        'tables-changed': (gobject.SIGNAL_RUN_LAST, gobject.TYPE_NONE, ()),
    }        

    exclude = "geometry_columns", "spatial_ref_sys"

    def __init__(self):
        gobject.GObject.__init__(self)
        self.tables = []
        self.finished = False # flag true when done
        self.__thread = None

    def load(self, db, verbose = False):
        if self.__thread:
            if self.__thread.isAlive():
                self.__stop = True
                self.__thread.join()

        self.__thread = threading.Thread(None,self.__loader,None,(db,verbose))
        self.__stop = False
        self.tables = []
        self.finished = False
        self.__thread.start()
        

    def wait_until_loaded(self):
        """ Convenience function for external, non-mezoGIS apps. """
        if self.__thread:
            if self.__thread.isAlive():
                self.__thread.join()

    def getTable(self, tableschema, tablename):
        for table in self.tables:
            if table.name == tablename and table.schema == tableschema:
                return table
        return None

    def getSortedByNameLength(self):
        t = [] + self.tables
        t.sort(lambda a, b: len(b.name)-len(a.name))
        return t

    def get_extent_from_table(self, db, table):
        c = db.handle.cursor()
        c.execute("COMMIT; VACUUM ANALYSE %s;"%table.getSQL())
        
        table.extent = None
        if db.functionAvailable("estimated_extent"):

            query = """SELECT estimated_extent(table_schema,table_name,column_name)
            FROM information_schema.columns
            WHERE data_type='USER-DEFINED' AND udt_name='geometry'
            AND table_schema='%s' AND table_name='%s';"""
            c.execute(query%(table.schema,table.name))
            for row in c.fetchall():
                try:
                    extent = Box()
                    extent.fromWKT(row[0])
                    if table.extent is None: table.extent = extent
                    else: table.extent = joinBoxes(table.extent,extent)                 
                except:
                    pass
                    
            
    def __loader(self, db, verbose):
        if verbose: print "get table information: "
        if not db.connected: return
        c = db.handle.cursor()

        #-----------------------------------------------------------------------
        # Find out which tables and columns the database contains.
        query = """
        select table_schema, table_name, column_name, udt_name FROM
        information_schema.columns WHERE
        table_schema != 'information_schema' AND table_schema != 'pg_catalog' 
        AND table_name != 'spatial_ref_sys' AND table_name != 'geometry_columns'
        ORDER BY table_schema, table_name;
        """
        c.execute(query)
        result = c.fetchall()
        for row in result:
            if self.__stop: return
            table = self.getTable(row[0],row[1])
            if table is None:
                table = Table(row[0],row[1])
                self.tables.append(table)
            
            col = [None] * 4
            col[0] = row[2] # name
            col[1] = row[3] # type
            col[2] = None   # extent as a GeoTypes.BBox
            col[3] = None   # SRID
            # the last two values are not critical, and should be fetched afterwards

            # make sure that "geometry" is lower case
            if col[1].lower() == "geometry":
                table.hasGeometry = True
                col[1] = "geometry"

            table.columns.append(col)
      
        gtk.gdk.threads_enter()  
        self.emit("minimal-info-gathered")
        gtk.gdk.threads_leave()  
        self.finished = True 



        #-----------------------------------------------------------------------    
        # Now get the extent of each geometry column with estimated_extent()

        estimate = db.functionAvailable("estimated_extent")
        tableList = [] # a list of tables to scan sequentially with extent()
        if verbose: print "Get extent with 'estimated_extent()':"
        if estimate:
            fetchManually = False

            # Note: extent is only available after a VACUUM ANALYSE (vacuumdb -z).
            # If the result set is empty, fall back to old procedure
            query = """SELECT table_schema, table_name, 
            estimated_extent(table_schema,table_name,column_name)
            FROM information_schema.columns WHERE data_type='USER-DEFINED' and
            udt_name='geometry';"""

            try:
                c.execute(query)
             
                result = c.fetchall()
                t = time.time()
                for row in result:
                    if self.__stop: return
                    table = self.getTable(row[0],row[1])
                    if table is None: 
                        if verbose: print "Table not registered:\n%s"%row

                    # try to get the extent from row[2]
                    wkt = row[2]
                    try:
                        extent = Box()
                        extent.fromWKT(wkt)

                        if table.extent is None: table.extent = extent
                        else: table.extent = joinBoxes(table.extent,extent)

                        if verbose: print "%s.%s ->"%(table.schema,table.name),
                        if verbose: print table.extent
                    
                    except: # if this fails, fall back to old procedure
                        if verbose: print "%s.%s -> no extent available"%(table.schema,table.name)
                        table.extent = None
                        tableList.append(table)

            except psycopg.ProgrammingError, e:
                print "Can't get estimated table extents."
                print e

            if verbose: print "time elapsed: %fsec"%(time.time()-t)

        else:
            if verbose: print "'estimated_extent()' is not available, fall back to sequential scan."
            # if 'estimated_extent' is not available, scan all tables with 'extent'
            for table in self.tables:
                tableList.append(table)

        if verbose: print   
            
        #-----------------------------------------------------------------------
        # Finally, scan the remaining, extentless tables for geometry extents
        #if verbose: print "Fallback: Get extent with 'extent()':"
        #for table in tableList:

            #t = time.time()
            #for col in table.columns:
                #if col[1].lower() == "geometry":
                    #c.execute('SELECT extent(%s) FROM "%s"."%s"' %(col[0],table.schema,table.name))
                    #r = c.fetchone()[0]
                    #try:
                        #table.extent = Box()
                        #table.extent.fromWKT(r)
                        #if verbose: print "%s.%s ->"%(table.schema,table.name),
                        #if verbose: print table.extent,"in %f seconds"%(time.time()-t)
                    #except Exception, e:
                        #if verbose: print "%s.%s -> no extent available"%(table.schema,table.name)
                        #if verbose: print r

        #if verbose: print

        #-----------------------------------------------------------------------
        # Now get a sample row from each of the tables...
        # Do this to find out which kind of geometry to expect from the table
        # geometry_columns seems not to automatically get created

        if verbose: print "get aestetical details about the table:"

        for table in self.tables:
            if self.__stop: return
            t = time.time()           

            colList = []
            for col in table.columns:
                colList.append('"%s"'%col[0])
  
            # fetch columns in the order they appear in the table's list
            # Not sure if this is nessesary, though.
            q = 'SELECT %s FROM %s LIMIT 1' %(",".join(colList),table.getSQL())
            try:
                c.execute(q)
                row = c.fetchone()
                if row != None:
                    for index, field in enumerate(row):
                        col = table.columns[index]
                        if col[1].lower() == "geometry":
                            col[2] = field.__class__.__name__
                            col[3] = field.SRID()
                if verbose: print "%s.%s in %f seconds"%(table.schema,table.name, time.time()-t)
                gtk.gdk.threads_enter()
                self.emit("tables-changed")
                gtk.gdk.threads_leave()
            except Exception,e :
                if verbose: print e
                db.handle.rollback()

        if verbose: print
        c.close()
        if verbose: print "table information refreshed"
        if verbose: print 

gobject.type_register(TableInformer)

#--------------------------------------------------------------------------------------------------------
class DatabasePane(gtk.VBox):
    """ show database information """

    shortcutCounter = 0



    def __init__(self, pgbutler):
        gtk.VBox.__init__(self)
        self.pgbutler = pgbutler

        self.info = TableInformer()
        self.info.connect("minimal-info-gathered",self.__fill_treeview)
        self.info.connect("tables-changed",self.__updateIcons)

        self.view = gtk.TreeView(gtk.ListStore(str, gtk.gdk.Pixbuf, gobject.TYPE_PYOBJECT))
        self.view.connect("button_press_event",self.__contextmenu)
        crt = gtk.CellRendererText()
        crp = gtk.CellRendererPixbuf()

        col = gtk.TreeViewColumn()
        col.pack_start(crp, False)
        col.pack_start(crt, True)
        col.set_attributes(crp, pixbuf=1)
        col.set_attributes(crt, text=0)

        self.view.append_column( col )
        self.view.connect("row-activated",self.__on_doubleclick)
        self.view.set_headers_visible(False)
        self.db = None

        self.sw = gtk.ScrolledWindow()
        self.sw.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_AUTOMATIC)
        self.sw.add(self.view)  
        self.sw.set_shadow_type(gtk.SHADOW_IN)      
        self.pack_start(self.sw)

        self.set_size_request(210,200)
        self.show_all()

        self.dbShortcuts = []
        action = gtk.Action("dbShortcuts","Quick SQL",None,None)
        self.pgbutler.ag_connected.add_action(action)
        ui_id = self.pgbutler.uimanager.add_ui_from_string("""
            <ui>
                <menubar name="MenuBar">
                    <menu action="Database">
                        <placeholder name="DatabasePlaceholder">
                            <menu action="dbShortcuts" />
                        </placeholder>
                    </menu>
                </menubar>
            </ui>""")
        self.addDbShortcut("SELECT * FROM %s;")
        self.addDbShortcut("DROP TABLE %s;")
        self.addDbShortcut("COMMIT; VACUUM %s;")
        self.addDbShortcut("COMMIT; VACUUM ANALYSE %s;")

        if os.access(Options.configpath+"quicksql.txt", os.R_OK):
            f = open(Options.configpath+"quicksql.txt","r")
            for line in f.readlines():
                line = line.strip()
                if line == "": continue
                if line[0] == "#": continue
                try: self.addDbShortcut(line)
                except Exception, e: print e

    def __del__(self):
        try:
            f = open(Options.configpath+"quicksql.txt","w")
            f.write("# this file is generated by mezoGIS\n\n")
            for (shortcut,a,b) in dbShortcuts:
                f.write(shortcut)
                f.write("\n")
        except Exception, e:
            print e

    def getSelectedTable(self):
        c = self.view.get_cursor()
        if c[0] == None: return None
        return self.info.tables[c[0][0]]

    def computeTableExtent(self, widget, table):
        """ Start sequential scan to get the extent of the table """

        if not self.db.connected:
            errorMessage(_("Not connected to database."),None,widget.get_toplevel())

        c = self.db.handle.cursor()
        extent = None
        for col in table.columns:
            if col[1].lower() == "geometry":
                c.execute('SELECT extent("%s") FROM %s' %(col[0],table.getSQL()))
                r = c.fetchone()[0]
                b = Box()
                b.fromWKT(r)
                if extent is None: extent = b
                else: extent = joinBoxes(extent,b)
        
        table.extent = extent
        c.close()

    def addDbShortcut(self, shortcut):
        if shortcut.find("%s") == -1:
            raise Exception, '"%s" must contain "%%s".'%shortcut

        DatabasePane.shortcutCounter += 1
        actionName = "dbshort_%i" % DatabasePane.shortcutCounter

        action = gtk.Action(actionName,shortcut,"",None)
        action.connect("activate",self.__dbOperation,shortcut)
        self.pgbutler.ag_connected.add_action(action)
        uiplaceholder = """
        <ui>
            <menubar name="MenuBar">
                <menu action="Database">
                    <placeholder name="DatabasePlaceholder">
                        <menu action="dbShortcuts">
                            <menuitem action="%s"/>
                        </menu>
                    </placeholder>
                </menu>
            </menubar>
        </ui>"""
        ui_id = self.pgbutler.uimanager.add_ui_from_string(uiplaceholder % actionName)
        self.dbShortcuts.append( [shortcut,ui_id,action] )
        
    def __dbOperation(self, widget, operation):
        table = self.getSelectedTable()
        if table is None: return
        print "run:",operation
        self.pgbutler.setQueryBuffer(operation%table.getSQL())

    def tableProperties(self, widget, table):
        """ Popup a window to show information about a table. """

        dialog = gtk.Dialog('Table Properties',
            widget.get_toplevel(), gtk.DIALOG_DESTROY_WITH_PARENT,
            (gtk.STOCK_CLOSE,0))

        dialog.set_has_separator(False)
        #dialog.set_position(gtk.WIN_POS_CENTER)
        
        box = gtk.Table(2,6)
        box.attach(createLabel("<b>Table:</b>"),0,1,0,1, gtk.EXPAND|gtk.FILL, gtk.EXPAND|gtk.FILL, 0, 5)
        box.attach(createLabel(table.name,True),1,2,0,1)
        box.attach(createLabel("<b>Schema:</b>"),0,1,1,2, gtk.EXPAND|gtk.FILL, gtk.EXPAND|gtk.FILL, 0, 5)
        box.attach(createLabel(table.schema,True),1,2,1,2)
        box.attach(createLabel("<b>Extent:</b>"),0,1,2,3)

        if table.extent is not None:
            s = "x: %s - %s" %(table.extent.getLowerLeft().getX(),table.extent.getLowerLeft().getY())
            box.attach(createLabel(s,True),1,2,2,3)            
            s = "y: %s - %s" %(table.extent.getUpperRight().getX(),table.extent.getUpperRight().getY())
            box.attach(createLabel(s,True),1,2,3,4)
            s = "width: %s height: %s" %(
                table.extent.getUpperRight().getX()-table.extent.getLowerLeft().getX(),
                table.extent.getUpperRight().getY()-table.extent.getLowerLeft().getY())
            box.attach(createLabel(s,True),1,2,4,5)
        else:
            h = gtk.HBox()
            h.pack_start(createLabel("unknown",True))
            b = gtk.Button("Get Extent")
            def computeExtent(w,table):
                self.computeTableExtent(w,table)
                dialog.destroy()
            b.connect("clicked",computeExtent,table)
            h.pack_start(b,False)
            box.attach(h,1,2,2,3)

        treeview = createTreeview( "Column", "Type", "SRID" )
        model = treeview.get_model()
        for c in table.columns:
            if c[2] == None: model.append([c[0],c[1],""])
            else: model.append( [c[0],c[2],c[3]] )
        sw = gtk.ScrolledWindow()
        sw.set_policy(gtk.POLICY_AUTOMATIC, gtk.POLICY_ALWAYS)
        sw.add(treeview)
        sw.set_size_request(300,-1)
        f = gtk.Frame()
        f.add(sw)

        dialog.vbox.pack_start(box,False)
        dialog.vbox.pack_start(f,True,True,10)
        dialog.show_all()
        dialog.run()               
        dialog.destroy()

    def refresh(self, db = None):
        if db != None: self.db = db
        if self.db.connected == False:
            self.view.get_model().clear()
            return

        self.info.load(db,Options.verbose)

    def __contextmenu(self, treeview, event):
        if event.button == 3:
            x, y = int(event.x), int(event.y)
            pthinfo = treeview.get_path_at_pos(x, y)
            if pthinfo is not None:
                path, col, cellx, celly = pthinfo
                treeview.grab_focus()
                treeview.set_cursor(path)
                table = self.getSelectedTable()

                # context is freshly created
                context = gtk.Menu()

                item = gtk.MenuItem("Open")
                context.append(item)
                item.connect("activate", lambda w,c: self.pgbutler.runQuery(c), 'SELECT * FROM %s'%table.getSQL())
                item.show()

                item = gtk.SeparatorMenuItem()
                context.append(item)
                item.show()

                sub = gtk.Menu()
                for (shortcut,a,b) in self.dbShortcuts:
                    item = gtk.MenuItem(shortcut%table.getSQL(),False)
                    sub.append(item)
                    item.connect("activate", lambda w,c: self.pgbutler.setQueryBuffer(c), shortcut%table.getSQL())
                    item.show()
                item = gtk.MenuItem("Quick SQL")
                item.set_submenu(sub)
                context.append(item)
                item.show()

                item = gtk.SeparatorMenuItem()
                context.append(item)
                item.show()

                item = gtk.MenuItem("Properties")
                context.append(item)
                item.connect("activate", self.tableProperties, table)
                item.show()

                context.popup( None, None, None, event.button, event.time)

    def __on_doubleclick(self, treeview, path, view_column):
        table = self.info.tables[path[0]]
        
        # when a table that contains geometry does not have a view extent,
        # inform the user about it and about how to improve speed
        if table.extent == None and table.hasGeometry:
            text = "mezoGIS does not know the view extent of the table %s.%s. " + \
                   "The extent will be computed dynamically while loading.\n"+ \
                   "\n" + \
                   "To improve the execution speed you can perform VACUUM ANALYSE on the table."
            text = text%(table.schema, table.name)
            
            dialog = gtk.MessageDialog(
                self.get_toplevel(),
                gtk.DIALOG_MODAL|gtk.DIALOG_DESTROY_WITH_PARENT,
                gtk.MESSAGE_INFO,
                0,
                text)
                
            dialog.add_button('VACUUM ANALYSE now', 1)
            dialog.add_button(gtk.STOCK_OK, 0)    

            response = dialog.run()
            if response == 1:
                try:
                    self.info.get_extent_from_table(self.db,table)
                except Exception, e:
                    errorMessage(_("Error while running VACUUM ANALYSE on %s."%table.name),
                        e,self.get_toplevel())
                  
            dialog.destroy()
            
        self.pgbutler.runQuery('SELECT * FROM %s'%table.getSQL())

    def __updateIcons(self, w=None):
        model = self.view.get_model()
        it = model.get_iter_first()
        while it is not None:
            table = model.get_value(it,2)
            if table: model.set_value(it,1, self.__getIcon(table.getGeomType()) )
            it = model.iter_next(it)
        self.view.queue_draw()

    def __fill_treeview(self, w=None):
        """ fill the treeview with table data  """
        model = self.view.get_model()
        model.clear()
        for table in self.info.tables:
            geom = table.getGeomType()
            model.append([table.schema+"."+table.name,self.__getIcon(geom),table])
        self.view.queue_draw()
        self.view.get_toplevel().show_now()
        print "database table created"

    def __getIcon(self, geometry):
        icons = {"polygon": gtk.gdk.pixbuf_new_from_file(Options.iconpath+"geom_poly.png"),
                 "line": gtk.gdk.pixbuf_new_from_file(Options.iconpath+"geom_line.png"),
                 "point": gtk.gdk.pixbuf_new_from_file(Options.iconpath+"geom_point.png"),
                 "multi": gtk.gdk.pixbuf_new_from_file(Options.iconpath+"geom_multi.png"),
                 None: None}
        return icons[geometry]
